--Setup the temp table
DECLARE @Parents TABLE (
	ParentsId INT IDENTITY (1,1) PRIMARY KEY,
	email varchar(max),
	email2 varchar(max),
	email3 varchar(max),
	email4 varchar(max),
	email5 varchar(max),
	email6 varchar(max)
)

--This would be the pull from your real Parents Table.
INSERT INTO @Parents 
SELECT
	NULL,'test@domain.com',' ',NULL,NULL,NULL
UNION ALL
SELECT
	NULL,'   ',NULL,NULL,NULL,'test2@domain.com'
UNION ALL
SELECT
	NULL,'',NULL,NULL,NULL,'test3@domain.com'
	
--Look at the data before we cleanse it
SELECT * FROM @Parents

--Take a look at what COALESCE looks like before the cleanse
SELECT ParentsId, COALESCE(email2,email3,email4,email5,email6) AS NewEmail FROM @Parents

--RUN the NULLIF
UPDATE @Parents SET 
	email2 = NULLIF(email2,' '),
	email3 = NULLIF(email3,' '),
	email4 = NULLIF(email4,' '),
	email5 = NULLIF(email5,' '),
	email6 = NULLIF(email6,' ')

SELECT * FROM @Parents

--Take a look at what COALESCE looks like after the cleanse
SELECT ParentsId, COALESCE(email2,email3,email4,email5,email6) AS NewEmail FROM @Parents